﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using NUnit.Framework;
using Universe.SimpleQueue.Samples;

namespace Universe.SimpleQueue.Tests
{
    class LocalDBConnection
    {
        public static string DefaultFolder = Environment.SystemDirectory.Substring(0, 3)
                                             + "\\LOCAL DB DATA\\Simple Queue Tests";

        public static string ConnectionString { get { return ConnectionStringLoader.Value; } }

        private static Lazy<string> ConnectionStringLoader = new Lazy<string>(() =>
        {
            string sqlCreateDb1 = string.Format(@"
CREATE DATABASE SimpleQueue
       ON (NAME = SimpleQueue_data, FILENAME = '{0}\SimpleQueue.mdf', SIZE = 32MB, MAXSIZE = 9999MB, FILEGROWTH = 5)
   LOG ON (NAME = SimpleQueue_log, FILENAME = '{0}\SimpleQueue.ldf', SIZE = 32MB, MAXSIZE = 9999MB, FILEGROWTH = 5MB);",
                DefaultFolder);

            string csMaster = @"Data Source=(localdb)\v11.0; Integrated Security=true; Pooling=false";
            var csTouch = csMaster + "; Initial Catalog=SimpleQueue";
            var csRet = @"Data Source=(localdb)\v11.0; Integrated Security=true; Pooling=true; Initial Catalog=SimpleQueue; Application Name=SimpleQueue Tests";

            object scalar;
            Exception exception;

            // Heat LocalDB instance
            TryExecuteScalar(csMaster, "Select @@VERSION", out scalar, out exception);

            // Heat LocalDB instance
            if (!TryExecuteScalar(csMaster, "Select @@VERSION", out scalar, out exception))
                throw new Exception("localdb\v11.0 isn't available");


            // Check DB & Table exists?
            if (TryExecuteScalar(csTouch, "Select Object_ID('SimpleQueue')", out scalar, out exception)
                && exception == null && !(scalar == null || scalar == DBNull.Value))
                return csRet;

            // Create DB
            if (!Directory.Exists(DefaultFolder)) Directory.CreateDirectory(DefaultFolder);
            TryExecuteScalar(csMaster, sqlCreateDb1, out scalar, out exception);

            // Apply Schema
            foreach (var sql in DDLResources.MSSQLDDL)
                TryExecuteScalar(csTouch, sql, out scalar, out exception);

            return csRet;
        });

        private static bool TryExecuteScalar(
            string connection,
            string command,
            out object result,
            out Exception exception)
        {
            result = null;
            exception = null;
            try
            {
                using (SqlConnection con = new SqlConnection(connection))
                using (SqlCommand cmd = new SqlCommand(command, con))
                {
                    con.Open();
                    result = cmd.ExecuteScalar();
                    return true;
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                Trace.WriteLine(command + Environment.NewLine + "Query failed:" + Environment.NewLine + ex);
                return false;
            }
        }

    }
}
